<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
	<title>Data Types | ElasticSearch 7.7 权威指南中文版</title>
	<meta name="keywords" content="ElasticSearch 权威指南中文版, elasticsearch 7, es7, 实时数据分析，实时数据检索" />
    <meta name="description" content="ElasticSearch 权威指南中文版, elasticsearch 7, es7, 实时数据分析，实时数据检索" />
    <!-- Give IE8 a fighting chance -->
    <!--[if lt IE 9]>
    <script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
    <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
    <![endif]-->
	<link rel="stylesheet" type="text/css" href="../static/styles.css" />
	<script>
	var _link = 'sql-data-types.html';
    </script>
</head>
<body>
<div class="main-container">
    <section id="content">
        <div class="content-wrapper">
            <section id="guide" lang="zh_cn">
                <div class="container">
                    <div class="row">
                        <div class="col-xs-12 col-sm-8 col-md-8 guide-section">
                            <div style="color:gray; word-break: break-all; font-size:12px;">原英文版地址: <a href="https://www.elastic.co/guide/en/elasticsearch/reference/7.7/sql-data-types.html" rel="nofollow" target="_blank">https://www.elastic.co/guide/en/elasticsearch/reference/7.7/sql-data-types.html</a>, 原文档版权归 www.elastic.co 所有<br/>本地英文版地址: <a href="../en/sql-data-types.html" rel="nofollow" target="_blank">../en/sql-data-types.html</a></div>
                        <!-- start body -->
                  <div class="page_header">
<strong>重要</strong>: 此版本不会发布额外的bug修复或文档更新。最新信息请参考 <a href="https://www.elastic.co/guide/en/elasticsearch/reference/current/index.html" rel="nofollow">当前版本文档</a>。
</div>
<div id="content">
<div class="breadcrumbs">
<span class="breadcrumb-link"><a href="index.html">Elasticsearch Guide [7.7]</a></span>
»
<span class="breadcrumb-link"><a href="xpack-sql.html">SQL access</a></span>
»
<span class="breadcrumb-link"><a href="sql-spec.html">SQL Language</a></span>
»
<span class="breadcrumb-node">Data Types</span>
</div>
<div class="navheader">
<span class="prev">
<a href="sql-syntax-show-tables.html">« SHOW TABLES</a>
</span>
<span class="next">
<a href="sql-index-patterns.html">Index patterns »</a>
</span>
</div>
<div class="section xpack">
<div class="titlepage"><div><div>
<h2 class="title">
<a id="sql-data-types"></a>Data Types<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/language/data-types.asciidoc">edit</a><a class="xpack_tag" href="https://www.elastic.co/subscriptions"></a>
</h2>
</div></div></div>
<div class="informaltable">
<table border="1" cellpadding="4px">
<colgroup>
<col class="col_1">
<col class="col_2">
<col class="col_3">
<col class="col_4">
</colgroup>
<tbody>
<tr>
<td align="center" valign="top"><p><span class="strong strong"><strong>Elasticsearch type</strong></span></p></td>
<td align="center" valign="top"><p><span class="strong strong"><strong>Elasticsearch SQL type</strong></span></p></td>
<td align="center" valign="top"><p><span class="strong strong"><strong>SQL type</strong></span></p></td>
<td align="center" valign="top"><p><span class="strong strong"><strong>SQL precision</strong></span></p></td>
</tr>
<tr>
<td align="center" colspan="4" valign="top"><p><span class="strong strong"><strong>Core types</strong></span></p></td>
</tr>
<tr>
<td align="center" valign="top"><p><a class="xref" href="null-value.html" title="null_value"><code class="literal">null</code></a></p></td>
<td align="center" valign="top"><p><code class="literal">null</code></p></td>
<td align="center" valign="top"><p>NULL</p></td>
<td align="center" valign="top"><p>0</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><a class="xref" href="boolean.html" title="Boolean datatype"><code class="literal">boolean</code></a></p></td>
<td align="center" valign="top"><p><code class="literal">boolean</code></p></td>
<td align="center" valign="top"><p>BOOLEAN</p></td>
<td align="center" valign="top"><p>1</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><a class="xref" href="number.html" title="Numeric datatypes"><code class="literal">byte</code></a></p></td>
<td align="center" valign="top"><p><code class="literal">byte</code></p></td>
<td align="center" valign="top"><p>TINYINT</p></td>
<td align="center" valign="top"><p>3</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><a class="xref" href="number.html" title="Numeric datatypes"><code class="literal">short</code></a></p></td>
<td align="center" valign="top"><p><code class="literal">short</code></p></td>
<td align="center" valign="top"><p>SMALLINT</p></td>
<td align="center" valign="top"><p>5</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><a class="xref" href="number.html" title="Numeric datatypes"><code class="literal">integer</code></a></p></td>
<td align="center" valign="top"><p><code class="literal">integer</code></p></td>
<td align="center" valign="top"><p>INTEGER</p></td>
<td align="center" valign="top"><p>10</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><a class="xref" href="number.html" title="Numeric datatypes"><code class="literal">long</code></a></p></td>
<td align="center" valign="top"><p><code class="literal">long</code></p></td>
<td align="center" valign="top"><p>BIGINT</p></td>
<td align="center" valign="top"><p>19</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><a class="xref" href="number.html" title="Numeric datatypes"><code class="literal">double</code></a></p></td>
<td align="center" valign="top"><p><code class="literal">double</code></p></td>
<td align="center" valign="top"><p>DOUBLE</p></td>
<td align="center" valign="top"><p>15</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><a class="xref" href="number.html" title="Numeric datatypes"><code class="literal">float</code></a></p></td>
<td align="center" valign="top"><p><code class="literal">float</code></p></td>
<td align="center" valign="top"><p>REAL</p></td>
<td align="center" valign="top"><p>7</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><a class="xref" href="number.html" title="Numeric datatypes"><code class="literal">half_float</code></a></p></td>
<td align="center" valign="top"><p><code class="literal">half_float</code></p></td>
<td align="center" valign="top"><p>FLOAT</p></td>
<td align="center" valign="top"><p>3</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><a class="xref" href="number.html" title="Numeric datatypes"><code class="literal">scaled_float</code></a></p></td>
<td align="center" valign="top"><p><code class="literal">scaled_float</code></p></td>
<td align="center" valign="top"><p>DOUBLE</p></td>
<td align="center" valign="top"><p>15</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><a class="xref" href="keyword.html" title="Keyword datatype"><code class="literal">keyword</code></a></p></td>
<td align="center" valign="top"><p><code class="literal">keyword</code></p></td>
<td align="center" valign="top"><p>VARCHAR</p></td>
<td align="center" valign="top"><p>32,766</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><a class="xref" href="constant-keyword.html" title="Constant keyword datatype"><code class="literal">constant_keyword</code></a></p></td>
<td align="center" valign="top"><p><code class="literal">constant_keyword</code></p></td>
<td align="center" valign="top"><p>VARCHAR</p></td>
<td align="center" valign="top"><p>32,766</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><a class="xref" href="text.html" title="Text datatype"><code class="literal">text</code></a></p></td>
<td align="center" valign="top"><p><code class="literal">text</code></p></td>
<td align="center" valign="top"><p>VARCHAR</p></td>
<td align="center" valign="top"><p>2,147,483,647</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><a class="xref" href="binary.html" title="Binary datatype"><code class="literal">binary</code></a></p></td>
<td align="center" valign="top"><p><code class="literal">binary</code></p></td>
<td align="center" valign="top"><p>VARBINARY</p></td>
<td align="center" valign="top"><p>2,147,483,647</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><a class="xref" href="date.html" title="Date datatype"><code class="literal">date</code></a></p></td>
<td align="center" valign="top"><p><code class="literal">datetime</code></p></td>
<td align="center" valign="top"><p>TIMESTAMP</p></td>
<td align="center" valign="top"><p>29</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><a class="xref" href="ip.html" title="IP datatype"><code class="literal">ip</code></a></p></td>
<td align="center" valign="top"><p><code class="literal">ip</code></p></td>
<td align="center" valign="top"><p>VARCHAR</p></td>
<td align="center" valign="top"><p>39</p></td>
</tr>
<tr>
<td align="center" colspan="4" valign="top"><p><span class="strong strong"><strong>Complex types</strong></span></p></td>
</tr>
<tr>
<td align="center" valign="top"><p><a class="xref" href="object.html" title="Object datatype"><code class="literal">object</code></a></p></td>
<td align="center" valign="top"><p><code class="literal">object</code></p></td>
<td align="center" valign="top"><p>STRUCT</p></td>
<td align="center" valign="top"><p>0</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><a class="xref" href="nested.html" title="Nested datatype"><code class="literal">nested</code></a></p></td>
<td align="center" valign="top"><p><code class="literal">nested</code></p></td>
<td align="center" valign="top"><p>STRUCT</p></td>
<td align="center" valign="top"><p>0</p></td>
</tr>
<tr>
<td align="center" colspan="4" valign="top"><p><span class="strong strong"><strong>Unsupported types</strong></span></p></td>
</tr>
<tr>
<td align="center" valign="top"><p><em>types not mentioned above</em></p></td>
<td align="center" valign="top"><p><code class="literal">unsupported</code></p></td>
<td align="center" valign="top"><p>OTHER</p></td>
<td align="center" valign="top"><p>0</p></td>
</tr>
</tbody>
</table>
</div>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p>Most of Elasticsearch <a class="xref" href="mapping-types.html" title="Field datatypes">data types</a> are available in Elasticsearch SQL, as indicated above.
As one can see, all of Elasticsearch <a class="xref" href="mapping-types.html" title="Field datatypes">data types</a> are mapped to the data type with the same
name in Elasticsearch SQL, with the exception of <span class="strong strong"><strong>date</strong></span> data type which is mapped to <span class="strong strong"><strong>datetime</strong></span> in Elasticsearch SQL.
This is to avoid confusion with the ANSI SQL types <span class="strong strong"><strong>DATE</strong></span> (date only) and <span class="strong strong"><strong>TIME</strong></span> (time only), which are also
supported by Elasticsearch SQL in queries (with the use of
<a class="xref" href="sql-functions-type-conversion.html#sql-functions-type-conversion-cast" title="CAST"><code class="literal">CAST</code></a>/<a class="xref" href="sql-functions-type-conversion.html#sql-functions-type-conversion-convert" title="CONVERT"><code class="literal">CONVERT</code></a>), but don’t correspond to an
actual mapping in Elasticsearch (see the <a class="xref" href="sql-data-types.html#es-sql-only-types"><code class="literal">table</code></a> below).</p>
</div>
</div>
<p>Obviously, not all types in Elasticsearch have an equivalent in SQL and vice-versa hence why, Elasticsearch SQL
uses the data type <em>particularities</em> of the former over the latter as ultimately Elasticsearch is the backing store.</p>
<p>In addition to the types above, Elasticsearch SQL also supports at <em>runtime</em> SQL-specific types that do not have an equivalent in Elasticsearch.
Such types cannot be loaded from Elasticsearch (as it does not know about them) however can be used inside Elasticsearch SQL in queries or their results.</p>
<p><a id="es-sql-only-types"></a>The table below indicates these types:</p>
<div class="informaltable">
<table border="1" cellpadding="4px">
<colgroup>
<col class="col_1">
<col class="col_2">
</colgroup>
<tbody>
<tr>
<td align="center" valign="top"><p><span class="strong strong"><strong>SQL type</strong></span></p></td>
<td align="center" valign="top"><p><span class="strong strong"><strong>SQL precision</strong></span></p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">date</code></p></td>
<td align="center" valign="top"><p>29</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">time</code></p></td>
<td align="center" valign="top"><p>18</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">interval_year</code></p></td>
<td align="center" valign="top"><p>7</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">interval_month</code></p></td>
<td align="center" valign="top"><p>7</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">interval_day</code></p></td>
<td align="center" valign="top"><p>23</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">interval_hour</code></p></td>
<td align="center" valign="top"><p>23</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">interval_minute</code></p></td>
<td align="center" valign="top"><p>23</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">interval_second</code></p></td>
<td align="center" valign="top"><p>23</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">interval_year_to_month</code></p></td>
<td align="center" valign="top"><p>7</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">interval_day_to_hour</code></p></td>
<td align="center" valign="top"><p>23</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">interval_day_to_minute</code></p></td>
<td align="center" valign="top"><p>23</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">interval_day_to_second</code></p></td>
<td align="center" valign="top"><p>23</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">interval_hour_to_minute</code></p></td>
<td align="center" valign="top"><p>23</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">interval_hour_to_second</code></p></td>
<td align="center" valign="top"><p>23</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">interval_minute_to_second</code></p></td>
<td align="center" valign="top"><p>23</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">geo_point</code></p></td>
<td align="center" valign="top"><p>52</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">geo_shape</code></p></td>
<td align="center" valign="top"><p>2,147,483,647</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">shape</code></p></td>
<td align="center" valign="top"><p>2,147,483,647</p></td>
</tr>
</tbody>
</table>
</div>
<h4>
<a id="sql-multi-field"></a>SQL and multi-fields<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/language/data-types.asciidoc">edit</a>
</h4>
<p>A core concept in Elasticsearch is that of an <code class="literal">analyzed</code> field, that is a full-text value that is interpreted in order
to be effectively indexed. These fields are of type <a class="xref" href="text.html" title="Text datatype"><code class="literal">text</code></a> and are not used for sorting or aggregations as their actual value depends on the <a class="xref" href="analyzer.html" title="analyzer"><code class="literal">analyzer</code></a> used hence why Elasticsearch also offers the <a class="xref" href="keyword.html" title="Keyword datatype"><code class="literal">keyword</code></a> type for storing the <em>exact</em>
value.</p>
<p>In most case, and the default actually, is to use both types when for strings which Elasticsearch supports through <a class="xref" href="multi-fields.html" title="fields">multi fields</a>, that is the ability to index the same string in multiple ways; for example index it both as <code class="literal">text</code> for search but also as <code class="literal">keyword</code> for sorting and aggregations.</p>
<p>As SQL requires exact values, when encountering a <code class="literal">text</code> field Elasticsearch SQL will search for an exact multi-field that it can use for comparisons, sorting and aggregations.
To do that, it will search for the first <code class="literal">keyword</code> that it can find that is <em>not</em> normalized and use that as the original field <em>exact</em> value.</p>
<p>Consider the following <code class="literal">string</code> mapping:</p>
<div class="pre_wrapper lang-js">
<pre class="programlisting prettyprint lang-js">{
    "first_name" : {
        "type" : "text",
        "fields" : {
            "raw" : {
                "type" : "keyword"
            }
        }
    }
}</pre>
</div>
<p>The following SQL query:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT first_name FROM index WHERE first_name = 'John'</pre>
</div>
<p>is identical to:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT first_name FROM index WHERE first_name.raw = 'John'</pre>
</div>
<p>as Elasticsearch SQL automatically <em>picks</em> up the <code class="literal">raw</code> multi-field from <code class="literal">raw</code> for exact matching.</p>
</div>
<div class="navfooter">
<span class="prev">
<a href="sql-syntax-show-tables.html">« SHOW TABLES</a>
</span>
<span class="next">
<a href="sql-index-patterns.html">Index patterns »</a>
</span>
</div>
</div>

                  <!-- end body -->
                        </div>
                        <div class="col-xs-12 col-sm-4 col-md-4" id="right_col">
                        
                        </div>
                    </div>
                </div>
            </section>
        </div>
    </section>
</div>
<script src="../static/cn.js"></script>
</body>
</html>